alter table jms_dwd.dwd_tmsnew_shipment_union_base_hi set TBLPROPERTIES('EXTERNAL'='False');
drop table jms_dwd.dwd_tmsnew_shipment_union_base_hi;
create external table jms_dwd.dwd_tmsnew_shipment_union_base_hi(
    id bigint comment'主键'
    ,shipment_no string comment'任务编号'
    ,vehicleline_type bigint comment'运营模式'
    ,business_attribute bigint comment'业务属性1:正班2:加班'
    ,shipment_name string comment'任务名称'
    ,vehicleline_id bigint comment'参考车线ID'
    ,vehicleline_code string comment'参考车线编码'
    ,vehicleline_name string comment'参考车线名称'
    ,shifts bigint comment'班次'
    ,transport_type string comment'运力类型'
    ,operation_model bigint comment'运行模式'
    ,vehicle_groupid bigint comment'规划车型组ID'
    ,vehicle_typegroup string comment'规划车型'
    ,actual_vehicle_groupid bigint comment'实际车型ID'
    ,actual_vehicle_typegroup string comment'实际车型'
    ,start_id bigint comment'始发网点ID'
    ,start_code string comment'始发网点编号'
    ,start_name string comment'始发网点名称'
    ,end_id bigint comment'目的网点ID'
    ,end_code string comment'目的网点编号'
    ,end_name string comment'目的网点名称'
    ,cnt bigint comment'途径地数量'
    ,mileage double comment'系统里程(KM)'
    ,total_runtime bigint comment'总运行时间(MIN)'
    ,standard_speed double comment'标准时速(KM/H)'
    ,total_intransittime bigint comment'总在途时间(MIN)'
    ,span_days bigint comment'跨越天数'
    ,actual_mileage double comment'实际系统里程(KM)'
    ,actual_total_runtime bigint comment'实际总运行时间(MIN)'
    ,actual_standard_speed double comment'实际时速'
    ,planned_departure_time timestamp comment'规划发车时间'
    ,planned_arrival_time timestamp comment'规划到车时间'
    ,actual_departure_time timestamp comment'实际发车时间'
    ,actual_arrival_time timestamp comment'实际到车时间'
    ,force_complete_time timestamp comment'强制完成时间'
    ,shipment_state bigint comment'任务状态,待调度0已调度1待执行2在途3已完成4已作废5'
    ,vehicle_class_id string comment'车种类ID'
    ,vehicle_class_name string comment'车种类名称'
    ,plate_number string comment'车牌号'
    ,trailer_number string comment'挂车号'
    ,carrier_id bigint comment'承运商ID'
    ,carrier_type smallint comment'承运商类型'
    ,carrier_name string comment'承运商全名'
    ,ori_reg_shift_carrier_id bigint comment'原正班承运商ID'
    ,vehicletype_id string comment'车型ID'
    ,vehicletype_name string comment'车型名称'
    ,load_weight double comment'核定载重(KG)'
    ,vehicle_volume double comment'核定体积（M³）'
    ,vehicle_door_cnt bigint comment'车门'
    ,driver_name string comment'司机名称'
    ,driver_contact string comment'随车电话'
    ,create_by bigint comment'创建人ID'
    ,create_by_name string comment'创建人姓名'
    ,create_time timestamp comment'创建时间'
    ,update_by bigint comment'更新人ID'
    ,update_by_name string comment'更新人姓名'
    ,update_time timestamp comment'更新时间'
    ,overtime_type tinyint comment'加班类型：11爆仓加班、12顶班、13应急加班'
    ,overtime_reasons string comment'加班原因说明'
    ,shipment_type bigint comment'任务类型1:自有正班2:外租正班3:自有加班4:外租加班'
    ,quotation_model bigint comment'报价模式1加班价2正班价'
    ,apply_reason string comment'申请原因'
    ,audit_status smallint comment'审核状态'
    ,audit_remark string comment'审核备注'
    ,audit_id bigint comment'审核人编号'
    ,auditer string comment'审核人'
    ,audit_date timestamp comment'审核时间'
    ,apply_reason_item smallint comment'申请原因固定选项(1临时加班、2顶班、3增加经停、4取消经停、5线路延伸、6其他)'
    ,pic_path string comment'微信上传任务图片（多张以,隔开）'
    ,pic_adr string comment'前端图片地址（多张以,隔开）'
    ,promotion tinyint comment'是否促销：1:是，2：否'
    ,freight_generate_status tinyint comment'运费流水生成状态：1已生产2未生成'
    ,billing_way tinyint comment'计费方式（1：整车计重、2：零担计重、3：按趟计费）'
    ,settlement_vehicle_type_id string comment'结算车型ID'
    ,settlement_vehicle_type_name string comment'结算车型名称'
    ,car_apply_code string comment'用车申请编码'
    ,bidding_demand_code string comment'招标需求编码'
    ,is_ballast tinyint comment'是否空放：1是2否'
    ,special_vehicle_type_id bigint comment'特殊车型id'
    ,special_vehicle_type_name string comment'特殊车型名称'
    ,remark string comment'备注'
    ,update_times bigint comment'修改次数'
    ,start_financial_center_id bigint comment'始发代理区ID'
    ,start_financial_center_code string comment'始发代理区CODE'
    ,start_financial_center_desc string comment'始发代理区名称'
    ,end_financial_center_id bigint comment'目的代理区ID'
    ,end_financial_center_code string comment'目的代理区CODE'
    ,end_financial_center_desc string comment'目的代理区Name'
    ,estimate_arrival_time timestamp comment'预计到达时间'
    ,opt_net_work_code string comment'操作人所属网点编码'
    ,opt_net_work_name string comment'操作人所属网点名称'
    ,opt_financial_center_code string comment'操作人所属代理区编码'
    ,opt_financial_center_desc string comment'操作人所属代理区名称'
    ,start_parent_code string comment'发车所属网点编码'
    ,start_parent_name string comment'发车所属网点名称'
    ,end_parent_code string comment'到车所属网点编码'
    ,end_parent_name string comment'到车所属网点名称'
    ,complete_time timestamp comment'完成时间'
    ,table_type tinyint comment'1-干线；2-支线'
) comment'支干线任务表-小时更新'
partitioned by (dt string comment '分区日期-任务规划发车时间')
stored as parquet
location '/dw/hive/jms_dwd.db/external/dwd_tmsnew_shipment_union_base_hi'
tblproperties(
'discover.partitions'='false',
'parquet.column.index.access'='true');
